package dao;

import org.omg.CORBA.Request;
import pojo.Manager;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author konley
 * @date 2020-06-09 22:09
 */
public class ManagerDao {
    //根据mid查所有
    public Manager getManager(int mid){
        Manager mana = null;
        Connection con = DBUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("select * from Manager where mid=?");
            ps.setString(1,mid+"");
            rs = ps.executeQuery();
            if(rs.next()){
                mana = new Manager();
                mana.setMid(rs.getInt("mid"));
                mana.setName(rs.getString("name"));
                mana.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(rs,ps,con);
        }
        return  mana;
    }
    //查询相关统计信息
    public List<Integer> selectDb(){
        //数据库统计集合
        List<Integer> lists = new ArrayList<>();
        Connection con = DBUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("select sum(money) as 'moneySum' from bill where type='消费'");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("moneySum"));
            ps = con.prepareStatement("select count(bid) as 'billSum' from bill");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("billSum"));
            ps = con.prepareStatement("select count(sid) as 'stuSum' from student");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("stuSum"));
            ps = con.prepareStatement("select count(cid) as 'cardSum' from card");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("cardSum"));
            ps = con.prepareStatement("select count(mid) as 'managerSum' from manager");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("managerSum"));
            ps = con.prepareStatement("select sum(money) as 'money1Sum' from bill where type='消费' and address='一食堂'");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("money1Sum"));
            ps = con.prepareStatement("select sum(money) as 'money2Sum' from bill where type='消费' and address='二食堂'");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("money2Sum"));
            ps = con.prepareStatement("select sum(money) as 'money3Sum' from bill where type='消费' and address='三食堂'");
            rs = ps.executeQuery();
            if(rs.next())
                lists.add(rs.getInt("money3Sum"));
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return lists;
    }
}
